﻿/*************************************************
 * 模块：
 * 功能：
 * 编写人：周磊
 * 编写时间：2015/7/6 11:06:14 
 * 修改人：
 * 修改时间：
 * **********************************************/

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using Dapper;
using MySql.Data.MySqlClient;

namespace WhereAmiMvc.Dbs
{
    public abstract class DBCommon<T> where T : new()
    {
        public abstract string TableName { get; }
        public abstract string PrimaryKey { get; }
        public abstract string ConnKey { get; }
        public abstract string ParaF { get; }

        public MySqlConnection GetCon()
        {
            MySqlConnection conn = null;

            conn = new MySqlConnection(ConfigurationManager.ConnectionStrings[ConnKey].ConnectionString);

            return conn;
        }

        public List<T> GetAll()
        {
            var sql = string.Format("SELECT * FROM {0}", TableName);

            var conn = GetCon();

            conn.Open();

            var list = new List<T>();

            try
            {
                if (conn.Query(sql).Any())
                {
                    list = conn.Query<T>(sql).ToList();
                }
            }
            catch (Exception)
            {
                conn.Close();


                throw;
            }

            conn.Close();

            return list;
        }

        public int Insert(T t)
        {
            var type = typeof (T);

            var cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetCon();

            var paraF = ParaF;

            foreach (var propertyInfo in type.GetProperties())
            {
                cols += propertyInfo.Name + ",";

                colsValue += paraF + propertyInfo.Name + ",";
            }
            cols = cols.EndsWith(",") ? cols.Substring(0, cols.Length - 1) : cols;

            colsValue = colsValue.EndsWith(",") ? colsValue.Substring(0, colsValue.Length - 1) : colsValue;

            var sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", TableName, cols, colsValue);

            var result = 0;

            conn.Open();

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertMulty(List<T> t)
        {
            var type = typeof (T);

            var cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetCon();

            var paraF = ParaF;

            foreach (var propertyInfo in type.GetProperties())
            {
                cols += propertyInfo.Name + ",";

                colsValue += paraF + propertyInfo.Name + ",";
            }
            cols = cols.EndsWith(",") ? cols.Substring(0, cols.Length - 1) : cols;

            colsValue = colsValue.EndsWith(",") ? colsValue.Substring(0, colsValue.Length - 1) : colsValue;

            var sql = string.Format("INSERT INTO {0}({1}) VALUES({2})", TableName, cols, colsValue);

            var result = 0;

            conn.Open();

            var trans = conn.BeginTransaction();

            try
            {
                for (var i = 0; i < t.Count; i++)
                {
                    result += conn.Execute(sql, t[i], trans, null, null);
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int Update(T t)
        {
            var type = typeof (T);

            var cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetCon();

            var paraF = ParaF;

            var upCol = string.Empty;

            foreach (var propertyInfo in type.GetProperties())
            {
                cols = propertyInfo.Name;

                colsValue = ParaF + propertyInfo.Name;

                upCol += cols + "=" + colsValue + ",";
            }
            upCol = upCol.EndsWith(",") ? upCol.Substring(0, upCol.Length - 1) : upCol;

            var sql = string.Format("UPDATE {0} SET {1}  WHERE {2}", TableName, upCol,
                PrimaryKey + "=" + paraF + PrimaryKey);

            var result = 0;

            conn.Open();

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateMulty(List<T> t)
        {
            var type = typeof (T);

            var cols = string.Empty;

            var colsValue = string.Empty;

            var conn = GetCon();

            var paraF = ParaF;

            var upCol = string.Empty;

            foreach (var propertyInfo in type.GetProperties())
            {
                cols = propertyInfo.Name;

                colsValue = ParaF + propertyInfo.Name;

                upCol += cols + "=" + colsValue + ",";
            }
            upCol = upCol.EndsWith(",") ? upCol.Substring(0, upCol.Length - 1) : upCol;

            var sql = string.Format("UPDATE {0} SET {1}  WHERE {2}", TableName, upCol,
                PrimaryKey + "=" + paraF + PrimaryKey);

            var result = 0;

            conn.Open();

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int Delete(T t)
        {
            var sql = "DELETE FROM {0} WHERE {1}";

            var Keys = PrimaryKey + "=" + ParaF + PrimaryKey;

            sql = string.Format(sql, TableName, Keys);

            var conn = GetCon();

            conn.Open();

            var result = 0;

            try
            {
                result = conn.Execute(sql, t);
            }
            catch (Exception exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteByKey(string key)
        {
            var sql = "DELETE FROM {0} WHERE {1}='{2}'";

            sql = string.Format(sql, TableName, PrimaryKey,key);

            var conn = GetCon();

            conn.Open();

            var result = 0;

            try
            {
                result = conn.Execute(sql);
            }
            catch (Exception exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }




        public List<T> GetByCondition(string fields, string Conditions)
        {
            var sql = string.Format("SELECT {0} FROM {1} WHERE 1=1 {2}", fields, TableName, Conditions);

            var conn = GetCon();

            conn.Open();

            var list = new List<T>();

            if (conn.Query(sql).Any())
            {
                list = conn.Query<T>(sql).ToList();
            }
            conn.Close();

            return list;
        }

        public DataSet GetDataTable(string[] fields, string Condition, string[] paras, object[] values
            , string Groupby, string Orderby)
        {
            var conn = GetCon();
            conn.Open();
            var adp = new MySqlDataAdapter();
            var com = new MySqlCommand();
            var sql = "SELECT {0} FROM {1} WHERE 1=1 {2}";
            var strFields = string.Empty;
            for (var i = 0; i < fields.Length; i++)
            {
                strFields += fields[i] + ",";
            }
            strFields = strFields.Substring(0, strFields.Length - 1);
            sql = string.Format(sql, strFields, TableName, Condition);
            com.CommandText = sql;
            if (paras != null)
            {
                for (var i = 0; i < paras.Length; i++)
                {
                    com.Parameters.AddWithValue(paras[0], values[0]);
                }
            }
            adp.SelectCommand = com;
            var ds = new DataSet();
            adp.Fill(ds);
            return ds;
        }

        public DataTable GetTableBySql(string sql)
        {
            var dt = new DataTable();

            var adp = new MySqlDataAdapter(sql, GetCon());

            adp.Fill(dt);

            return dt;
        }

        public int DosthTrans(string sql1, string sql2, object obj1, object obj2)
        {
            var result = 0;
            using (var conn = GetCon())
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        conn.Execute(sql1, obj1, trans);
                        result = conn.Execute(sql2, obj2, trans);
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                    }
                }
            }
            return result;
        }

        public T GetByKey(string key)
        {
            string sql = @"Select * from {0} WHERE {1}='{2}'";

            sql = string.Format(sql, TableName, PrimaryKey, key);

            T t = default(T);

            using (var conn = GetCon())
            {
                if (conn.Query(sql).Any())
                {
                    t = conn.Query<T>(sql).Single();
                }
            }
            return t;
        }
    }
}